Cannot Alias Locked Tables / Join To Unlocked Tables ?

Tables Locked
I used mysql-3.23. I run "large" large query and they don't crashed.
When I run the same query on mysql 4.1 some tables are locked.

SELECT actor.first_name, actor.last_name,
A.dba, A.source, A.processor, A.partner, A.date_send, A.a_status, A.date_received A.notes,C.request, C.request2, C.request, C.month, C.submitted,
D.status, D.processor, D.approve, D.month, D.contract,
B.b, B.switch, B.var, B.download, B.follow_up
FROM actor, A, C, D, B
where actor.id = A.actor_id and C.a_id = A.id and
D.a_id = A.id and B.a_id = A.id;

View Replies !   View Related

---

Show The LOCKED Tables
Can I show the locked tables which any client might have locked on mysqld at any given time?

View Replies !   View Related

---

Tables Locked During Indexing
I have an application that I work on that access' customer information via a MySql database. Recently we have had performance issues so we have had an index created for us. When deploying the new index we have noticed an issue where our application will receive an 'Sql Syntax error or Access Violation'. Now initally we thought this was a code problem until by mistake i had neglected to run the Index on one paticular machine that is running our application and they worked perfect. During Mid Day i decided to run the Index query and All of a sudden we started recieving the error again. I am wondering if when running the index, does MySql lock the table so that no read/write can be performed? If this is the case is there a way in either MySql Administrator or another tool that i can see what the status of the index is? (ie.. Completed, Percent completed or whatever)

View Replies !   View Related

---

Corrupt Database :: Tables Getting Locked
We have been having issue with mysql database because the tables are getting locked once in a while. i run huge process which keep running lots of queries for hours.

mysql> select count(*),date from IVbidask where date > 20090226 group by date order by date; ....

View Replies !   View Related

---

Replication And SELECT Locking, Tables Not Locked
My entire team has been banging their heads against the wall on this for months. Here's the situation:

We have two servers in a standard master-slave setup.
There are 4 tables on the master that are replicated to the slaves
There are INSERT and UPDATE commands going through on the master
There are SELECT commands running on the slave.
These are the only commands running on either box.

The INSERTS are capable of inserting 20,000 rows per second.
The UPDATES are also capable of updating 20,000 rows per second.
The SELECTS can take even more data per second.

The issue is, the UPDATE from replication and the SELECT from the client are deadlocking, but the tables don't lock!

Code: .....

View Replies !   View Related

---

How To Get The SUM Value From Two Tables? Join Tables? Subquery?
Hi,
I have two tables:

table A

Id. | id_result | value
1 | 1| 10
2 | 1| 11
3 | 2| 7
4 | 2| 13

table B

Id. | id_result | value
1 | 1| 4
2 | 1| 1
3 | 2| 5
4 | 2| 6


How can I get sum of unique keys from table A and B (id_result) like this?:

id_result | sum_table_A | sum_table_B
1 | 21| 5
2 | 20| 11

I can do it with UNION or 2 separate SQL statement, but how to make it in 1 query or using subquery?

View Replies !   View Related

---

Connecting Three Tables With Left Join And Ordinary Join
I have 3 Mysql tables:

Week (with columns day and hour)
Activity (with columns day, hour, activityid and ac_text)
Person (with columns name and activityid)

I would like to create a scheme showing the activities during a week sorted on days and hours. If I ignore the person table I can fix it with the statement:
Select …. From week left join activity on (week.day = activity.day) and (week.hour = activity.hour) order by day, hour

I can then make a loop (I am usin asp.net) that writes the activities.
My problem is when I try to combine the persons to the activtities in an given hour. How do I do that ? (activity.activityid = person.activityid).

I have a little extra question. When I make the join above and print the result (day, time and activity) there isn’t any output if no activity matches a given day and hour. How do I do when I always want to print day and hour and add activity where such exist.

View Replies !   View Related

---

Use An Alias Name For A Field In A Join Ation
I'm using a select like this:

SELECT * FROM `CPESER` INNER JOIN `CONCPE` ON `CPESER`.`id_concpe` = `CONCPE`.`id`
LEFT JOIN `CADSER` ON `CPESER`.`id_cadser` = `CADSER`.`id` LEFT JOIN `CADPRO`
ON `CADSER`.`id_cadpro_data`= `CADPRO`.`id` AND
`CADSER`.`id_cadpro_voice` = `CADPRO`.`id` LEFT JOIN `CONASS` ON
`CONCPE`.`contract` = `CONASS`.`contract` INNER JOIN `CADCLI` ON
`CONASS`.`id_cadcli` = `CADCLI`.`id` ORDER BY `CADCLI`.`name`

but there is a problem: some colums has the same name... it's possilbe to use aliases to the fields?

View Replies !   View Related

---

Strange Alias And Join Problem On Server But Not Dev Box
I'm running MySQL 5.0.45, php5 and apache 1.3 on windows xp for development. All my tables are MyIsam.
The following query works fine :

MySQL
SELECT
 i.image_id, image_filename, region_name, monument_name
FROM
 image AS i
LEFT JOIN
 image2region USING(image_id)
LEFT JOIN
 region USING(region_id)
LEFT JOIN image2monument AS i2m ON (i.image_id=i2m.image_id)
LEFT JOIN monument USING(monument_id)
WHERE region_name='egypt'
LIMIT 0, 8
On the server (MySql v5.0.15) where I plan to host the site I get the following error:

#1054 - Unknown column 'image_filename' in 'field list'

If I give all the tables aliases and prepend all the column names after the SELECT with the aliases it works but I don't want to have to do this with all the queries I've written. I don't understand why it doesn't work on my dev box but not the hosts server.

I have also noticed that this problem only seems to arise on queries with more than two joins.

Has anyone else experienced a problem like this?

Or should I be prepending all columns with aliases as a matter of good practise anyway?

Cheers x

View Replies !   View Related

---

Join Two Tables
I have a database that has two tables and I'd like to be able to join more than one column in one table to one column in the other table....

View Replies !   View Related

---

How To Join 2 Tables
Anyways Im a newbie using MYSql V5

I have 2 tables (called teams & results)

In teams I have 2 columns

teamsindex..........teamname
(Primary Key)
........1......................arsenal
........2......................man u
........3......................liverpool
........4......................chelsea
........5......................tottenham
........6......................accrington stanley (who are they :) )

In results I have 3 columns (amongst many) which contain primary key & fixture info (intiger)


e.g.

Recordnum..........hometeam.......awayteam
(primary key)

.........1......................1.......................2
.........2......................3.......................4
.........3......................5.......................6

I want to output the fixtures so instead of it displaying 1vs2, 3vs4, 4vs5 I want it to display arsenal vs man u, liverpool vs chelsea, tottenham vs accrington stanley etc

I know there is a really really simple answer to this but Im really really thick, I expect its based on the JOIN syntax but can someone show me the code that would produce the results I require in order for me to get my head around it, me is very odd, I can work things out going backwards.

View Replies !   View Related

---

How To Join Tables?
I would like to select info from just two tables. I've tried two seperate select calls but it gives me some of the same info from each table twice.

Can someone please post a "join for dummies" example on how I would do this.

I've tried using "Union" but I'm still just a newbie and was even more confused.

View Replies !   View Related

---

Join Three Tables
I have a problem about joining three tables A, B, C:

table A has the serial_number as its primary key,
table B has the serial_number as its primary key but serial_number is not unique in this table (combined with category, group etc. other columns as primary key together )
table C has some common columns with table B

Now I want to select the serial_number from table B with certain conditions from Table C, so I could use the following query:
select distinct tB.serial_number from table_B as tB join table_C as tC Where Conditions;

And then, I would like to add the serial_number in table_A but not in table B to the serial_number I just selected, so I would use something like:

select distinct tB.serial_number from table_B as tB join table_C as tC Where Conditions
UNION
select serial_number from table_A not in table_B;

is it right? I think it's the right way to do that.

Another question, with the same task, can I do the query like this:

select tA.serial_number from table_A as tA LEFT JOIN table_B as tB ON tA.serial_number = tB.serial_number JOIN tableC as tC WHERE Conditions..

That's what I understand about "LEFT JOIN", SQL will select serial_number from table_B and table_C with the conditions satisfied first and then join table_A with serial_number only in table_A but not in table_B. Am I right?

View Replies !   View Related

---

Trying To Join 3 Tables
I ma trying to create a join on 3 tables. However I can't get it to work.

If I do this it works

SELECT * FROM Cottage, Cottage_Photos, Location WHERE Cottage.Cottage_ID = Cottage_Photos.Cottage_ID AND Cottage.Location_ID = Location.Location_ID;

However once i try this i just keep getting an empty set result

SELECT * FROM Cottage, Cottage_Photos, Location WHERE Cottage.bedrooms = '3’ AND Cottage.Cottage_ID = Cottage_Photos.Cottage_ID AND Cottage.Location_ID = Location.Location_ID;

Is it possible to join 3 tables?

View Replies !   View Related

---

Join Tables With Ip
i want to join two tables using ip address through sql

select * from db1.table1 union select * from db2.table2;

this is used for join two tables from the same pc. what command is used for joining the tables if they are not in same pc ?

View Replies !   View Related

---

Join Four Tables
SELECT appointment.appt_id, patient.pt_fname, patient.pt_lname, patient.pt_phonenum, appointment.appt_date, appointment.appt_time, language.language FROM appointment, language, patient where patient.lang_id = language.lang_id and patient.pt_id = appointment.pt_id

added requester to sql statement

SELECT appointment.appt_id, patient.pt_fname, patient.pt_lname, requester.req_fname, requester.req_lname, patient.pt_phonenum, appointment.appt_date, appointment.appt_time, language.language FROM appointment, language, patient where patient.lang_id = language.lang_id and patient.pt_id = appointment.pt_id and requester.req_id = patient.req_id

View Replies !   View Related

---

Join 3 Tables
i want to join three tables and i got it to do that but when want to see the results if there is not a record in all three tables the data for that person wont show up only the person who has a record in all three tables will.

SELECT * FROM users,user_info,user_contact where users.id = user_info.user_info_users_id and user_info.user_info_id = user_contact_id

how can i get it to show all the data and just show blank space if there is no record in one table. like the user_contact table.

View Replies !   View Related

---

How Do I Join These Two Tables?
I want to join two different queries into the same table. I have had a look at UNION and JOIN but they seem to be way over my head. Off peak time is Midnight to 7AM and Peak time is 7AM to Midnight. I can put the data into two separate tables (see below) but I don't know how to combine them. Code:

View Replies !   View Related

---

How To Join Two Tables
I have two tables, dtbl and dltbl

I want to retrieve dtbl.name when dtbl.id is the same as dltbl.id

This is giving me a syntax error:

SELECT t1.id, t2.name FROM dltbl AS t1 WHERE did = $id AND aid = {$a->id} INNER JOIN dtbl AS t2 ON t1.id = t2.id

View Replies !   View Related

---

Join 2 Tables
table1 = a1, a2 and a3
table2 = b1, b2 and b3
when join table1 and table2
table1 + table2 = a1, a2, a3, b1, b2 and b3

question:
Instead of having 6 collums after join, can I have 3??? (table1 + table2 = c1, c2 and c3)

View Replies !   View Related

---

Need To Join 15 Tables Together
joining 15 tables together in the database i am creating. I will need to later search the database by date for each table. how would i go about doing this? what kind of Id should i add to each table to have them "joined"?

View Replies !   View Related

---

Duplicate Alias/table Error When Using LEFT JOIN
getting a error stating that I am using a duplicate alias/table..

PHP Code:

 SELECT
    concat(a.first_name,' ',a.last_name) AS name,
    a.customer_id,
    a.phone_mobile,
    a.date_of_birth,
    concat(b.first_name,' ',b.last_name) AS sponsor,
    concat(c.first_name,' ',c.last_name) AS closing
FROM
    customer AS a,
    (customer AS b LEFT JOIN a ON b.customer_id=a.sponsor_customer_id)
    (customer AS c LEFT JOIN a ON c.customer_id=a.closing_customer_id)
WHERE a.first_name LIKE 's%'
ORDER BY a.first_name ASC; 

View Replies !   View Related

---

Join Tables Vertically
i have two tables with some common column names, i want to create a table joining these tables vertically

ex.

one table contain deposits and another contain withdrawals, both tables have date and amount columns. other columns are different.

i want to select date and amount columns from both tables and view it as a single table, so i can sort it or get the sum

please let me know whether it is possible and how?

View Replies !   View Related

---

Exclusive Join On Two Tables

table1

id --- data
1 --- john
2 --- william
3 --- russell
table 2
id --- table1_id
1 --- 3
2 --- 1
My query should just give me "william" as that's the only data in table1 which does not have it's id in table2

View Replies !   View Related

---

Left Join On More Than Three Tables
I am comfortable joining two tables to return data from several columns of each, and I'm comfortable left joining three tables when the second table is a "link" table that connects the primary key of the first table records with the foreign key of the third table's records.

My new project involves seven tables. Four of the tables are "data tables" and contain member's contact info. The first and center piece of those four tables lists the member_id, along with last name, first name and gender. The other three data tables have two columns each and list city_id and city, email_id and email_address, and phone_id and phoneNumber.

The remaining three tables of the seven are what I call "link" tables" (I never learned the real name for them). They simply link or connect the member_id (and ergo the member) to either that person's city, email address or phone number in the three smaller data tables. Each link table has two columns, one of which is the member_id, and the second of which is either the city_id, email_id or phone_id, depending on the table.

I set it up this way because (I think) proper db design specifies separate tables if not everyone has a phone, or email, or... It prevents redundancy, and it prevents empty fields in data rows -- all the things we love about normalization.

However, I don't know how to write a SELECT query that properly connects the seven tables so that I can query a person's name, gender, city, phone and email address. Name and gender come from the first table, and city, phone and email come from the other three data tables via connection with the three link tables. Can you point me in the right direction, or suggest an online tutorial that covers this (for me) complex design?

View Replies !   View Related

---

How To Use Left Join For 3 Or More Tables.
I am using mysql 4.1

SelectCustomer NameCustomer POSalesOrder POProduct Name

1.CHARLES 270086 05480 BCSLS
2.CHARLES 270086 05481 BCSLS
3.
4.

4 tables totally connected.

Transaction tables - 2
salesordermaster
salesordertran

master tables - 2
customer
products

i ll fetch the customer name from customer master table with reference from salesordermaster.
i ll fetch the product name from the product master table with reference from salesordertran.

see , the customerpo are same for above 2 records, it is from salesordermaster table.
and salesorderpo are different for above 2 records, it is from salesordertran table.

if none of the records are in salesordertran table, i need the display atleast customername and customerpo which is from salesordermaster.

(so far, i had tried with left join between salesordermaster and salesordertran, but salesordertran reference the product master table for the productname, so the resultant query fetches some records with cross product. is there any iff conditions in mysql?? can be used it here?)

View Replies !   View Related

---

Join Two Tables Completely
I want to join two tables completely. Here is an example:
table 1:
id spend
1 200
3 500

table 2:
id income
1 400
5 600

I want to get:
id spend income
1 200 400
3 500 0
5 0 600.

Is there a easy way to do it using mysql?

View Replies !   View Related

---

Join Tables On Other Servers
Is it possible to, for example, join a table in a database on another server in a select?

View Replies !   View Related

---

Join Tables And Set Prefixes
Is there a way to join tables and generate prefixes automatically?

I want to join three tables with columns with identical names. My tables are much bigger, but these are the columns with identical names.

Table 1: pictures_bath
id INT NOT NULL
picture VARCHAR(250)
thumb VARCHAR(250)

Table 2: pictures_kitchen
id INT NOT NULL
picture VARCHAR(250)
thumb VARCHAR(250)

Table 3: pictures_livingroom
id INT NOT NULL
picture VARCHAR(250)
thumb VARCHAR(250)

I want to select * from table a, b and c and join them with the object-table on column 'id'.

Is there a way to get the result with columns like this:
- bath_picture
- bath_thumb
- kitchen_picture
- kitchen_thumb
...

View Replies !   View Related

---

Outer Join 3 Tables
I am trying to join 3 tables:
t1 and t2 have an inner join at t1.t1_t2 = t2.t1_t2
t1 and t3 have a left outer join at t1.t1_t3 = t3.t1_t3

For some reason, the synatx is not working for me.

View Replies !   View Related

---

SELECT / JOIN Two Tables
I've seen several ways to make a "join" of 2 tables, and I don't know which is best practice. I only have some small tables (at the moment) to use it on, so I can't tell by the speed ... but maybe I could programm it better...

First method, which I normally use:

SELECT * FROM tbl1 JOIN tbl2 on tbl1.ID = tbl2.ID

Another way I've seen:

SELECT * FROM tbl1, tbl2 WHERE tbl1.ID = tb2.ID

What is the best, or is this depending on the purpose ?

View Replies !   View Related

---

Join 2 Tables From Different Databases
I need to join 2 tables from different databases for creating one SELECT query and get data from both tables.
Mechanism LEFT JOIN don't allow to do this or may be I don't see it.
Can I join and if I can, how can I do this?

View Replies !   View Related

---

Inner Join :: Compare Tables
Here is what I need to do- I have 2 tables related by an ID (int) field. I need to return all fields from one table and none from the second but I need to run a comparison against a field on the second table and if the field data equals 'x' then the corresponding record from the first table should not be returned. Seems simple to just say it, lol. I changed the way I was querying the db in the middle of the development so this particular situation wasn't anticipated when I created the db. Code:

View Replies !   View Related

---

Multiple Tables With Join
$queryDelete = "
DELETE FROM table1,table2
WHERE table1.id=".$_GET['id']."
AND table2.id=".$_GET['id'];
Looks ok, but doesnt work.....$_GET['id'] is read correctly and available in all tables.

View Replies !   View Related

---

What Join To Use With Multiple Tables?
I'm assuming I need some sort of join to accomplish this but I've yet to find something that I have been able to understand.

I have 3 tables - Items, Photos and a table that links the two that I've elegantly named ItemsPhotos

Items
- ItemID
- ItemName
- ItemEtc

Photos
- PhotoID
- PhotoFilename
- PhotoEtc

ItemsPhotos
- ItemPhotoID
- ItemID
- PhotoID

Each Item has a unique ID, as does each Photo. The ItemsPhotos keeps track of their relationship.

I want to be able to select all of the items and only one of the the available photos listed for that item to be returned to an Array for use like the following;

foreach(#){
$databaseOutput[#]['ItemName'];
$databaseOutput[#]['PhotoFilename'];
}

If anoyone knows how I can accomplish this I would be very grateful for a solution.

Sincere appologies if there is an obvious answer or if I have mis-posted - I've been trying to get this (among other things) to work for hours now and I'm desperate for a solution.

View Replies !   View Related

---

Join Help – 2 Tables, 3 Queries?
Hi all – just hoping someone can offer me some advice on a query that I just can’t get my head around. For reference I’m using MySQL 3.23.

The tables I’m dealing with are;

CREATE TABLE `category` (
`cat_id` int(11) NOT NULL auto_increment,
`cat_issub` int(11) NOT NULL default Ɔ',
`cat_name` varchar(255)
PRIMARY KEY (`cat_id`)
)

CREATE TABLE `site` (
`site_id` int(11) NOT NULL auto_increment,
`cat_id` int(11) NOT NULL default Ɔ',
`city_id` int(3) NOT NULL default Ɔ',
PRIMARY KEY (`site_id`)
)

What I need to do is;

•Find everything in `site` that has a particular city_id
•Find all the DISTINCT categories, `site`.`cat_id`, that all the sites belong to.
•(Here’s the bit I can’t do) If a the category id has a `cat_issub` != 0 then include it’s parent category in the results. The parent category’s cat_id (same table) is equal to `cat_issub`
•Only display the `cat_id`’s in the results where `cat_issub` = ‘0’

The SQL I came up with is a join, however, I can’t get it to bring up the last two points of the query’s requirements. I’m not even sure if this is at all possible, or think of an alternative PHP or MySQL function, so any help or suggestion will be greatly appreciated!

SELECT DISTINCT `cat1`.`cat_id`, `cat1`.`cat_issub`, `cat1`.`cat_name`, `cat1`.`cat_hassub` FROM `category`, `site` JOIN `category` AS `cat1` ON (`site`.`cat_id`) LEFT JOIN `category` AS `cat2` ON (`cat1`.`cat_issub`) WHERE `site`.`city_id` = '$city_id' AND (`site`.`cat_id` = `cat1`.`cat_id` OR `cat1`.`cat_id` = `cat2`.`cat_issub`) ORDER BY `cat_name` ASC

View Replies !   View Related

---

Join Tables By Referance
mysql> describe atf;
+----------------+--------------+------+-----+---------+----------------+
| Field
| customer_id | int(11) | NO | PRI | NULL | auto_increment |
| date | varchar(50) | YES | | 0 | |
| pin | char(3) | YES | | NULL | |
| ccd | char(3) | YES | | NULL | |
| company_name | varchar(16) | YES | | NULL | |
| fed_id | varchar(10) | YES | | NULL | |
| trans_desc | varchar(10) | YES | | NULL | |
| disc_data | varchar(20) | YES | | NULL | |
| effective_date | varchar(20) | YES | | NULL | |
| customer_name | varchar(100) | YES | | NULL | |
| rout_no | varchar(10) | YES | | NULL | |
| account_no | varchar(20) | YES | | NULL | |
| account_type | char(1) | YES | | NULL | |
| trans_type | char(1) | YES | | NULL | |
| optional_id | varchar(100) | YES | | NULL | |
| dollars | varchar(10) | YES | | NULL | |
| misc_info | varchar(100) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
17 rows in set (0.19 sec)

mysql> describe atf2
-> ;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| record_id | int(11) | NO | PRI | NULL | auto_increment |
| customer_id | int(11) | YES | | 0 | |
| effective_date | varchar(50) | YES | | 0 | |
| record_date | varchar(100) | NO | | | |
| load_id | varchar(100) | YES | | NULL | |
| dollars | varchar(10) | YES | | NULL | |
| misc_info | varchar(100) | YES | | NULL | |
| month | varchar(100) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
8 rows in set (0.06 sec)


How do i say look at table atf2 and take the customer_id (2nd Field) and reference atf customer_id which is the same and pull the company_name that equals the customer_id. All im trying to do is say from atf2 i have stored the customer_id of 1287 and after normalizing atf2 i would like to just get the company_name by using the customer_id from atf2. Does this make sense. I am sure its easy but i have looked at examples but im not getting it.

the company_name for customer_id=1287 is Jacks Transport in atf.

View Replies !   View Related

---

Help Needed To Join Tables.....
I'm starting into mysql these days and I have some tables created

function_detail
+------------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+------------------+------+-----+---------+----------------+
| function_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| function_name | varchar(128) | NO | | | |
| function_description | varchar(512) | NO | | | |
| function_image | varchar(256) | NO | | | |
| function_image_caption | varchar(128) | NO | | | |
| function_extras | varchar(64) | NO | | | |
+------------------------+------------------+------+-----+---------+----------------+

option_detail
+----------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+----------------+
| option_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| option_name | varchar(64) | NO | | | |
| option_description | varchar(256) | NO | | | |
| option_image | varchar(256) | NO | | | |
| option_image_caption | varchar(128) | NO | | | |
| option_extras | varchar(64) | NO | | | |
+----------------------+------------------+------+-----+---------+----------------+

product_detail
+---------------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |Extra |
+---------------------------+------------------+------+-----+---------+----------------+
| product_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| product_name | varchar(64) | NO | | | |
| product_short_description | varchar(256) | NO | | | |
| product_long_description | varchar(512) | NO | | | |
| product_series | varchar(64) | NO | | | |
| product_heads | varchar(64) | NO | | | |
| product_family | varchar(64) | NO | | | |
| product_category | varchar(64) | NO | | | |
| product_image | varchar(256) | NO | | | |
| product_image_caption | varchar(128) | NO | | | |
| product_catalog | varchar(512) | NO | | | |
| product_status | varchar(64) | NO | | | |
+---------------------------+------------------+------+-----+---------+----------------+

product_function_detail
+-------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| product_id | int(10) unsigned | NO | | 0 | |
| function_id | int(10) unsigned | NO | | | |
+-------------+------------------+------+-----+---------+-------+

product_option_detail
+-------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| product_id | int(10) unsigned | NO | PRI | 0 | |
| function_id | int(10) unsigned | NO | | | |
+-------------+------------------+------+-----+---------+-------+

product_technology_detail
+---------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+-------+
| product_id | int(10) unsigned | NO | PRI | 0 | |
| technology_id | int(10) unsigned | NO | | | |
+---------------+------------------+------+-----+---------+-------+
these tables are meant for: (let's see if I can explain correctly)
Product Details would contain all the details for a certain products
Function Details would contain all the functions performed by these products.
Technology Detail would contain all the technology embeeded in these products.

Some products may have either the same technology or the same functions (with the same descriptions) but affirmatively not the same product details (description product name & etc).

For Instance
Product A would perform function a,b,c,d and e and would have technology X,Y and Z
product B would perform function f,g and h and would have technology W, Y and Z.
product C would perform function b,c and I and would have technology O,P,Q and X.

How can I link the three tables?
Meaning that product_detail ---> function_detail when the linkage between the two of them resides in product_function_detail?

Here is something I've tryied

SELECT function_id, product_id as producto FROM product_function_detail WHERE product_id = ƈ' ORDER BY function_id asc;

SELECT distinct function_name, function_description, function_detail.function_id
FROM (select function_id, product_id as producto from product_function_detail
WHERE product_id = ƈ' ORDER BY function_id asc)as xref, function_detail
WHERE producto=ƈ'
BUT so far, no luck.

View Replies !   View Related

---

Performing Join On Three Tables
I want to extract data from three different tables, and I was wondering if I could use joins then all I would require is the execution of just one query. I have read that it's possible to perform more than two joins, but I could not find an example on the net... Can anybody show me an example or possibly give me like to a website where it has been explained already?




View Replies !   View Related

---

Join Tables, Same Column Name
I need to join 2 tables and to obtain their id's. But both tables has same column name for id, = "id". So in a 'while' loop there is no difference between them (i.e. $row['id']). Should I change these names to be different or is there another way to do this?





View Replies !   View Related

---

Need Query To Join 2 Tables Through A 3rd
I have a table called client (primary key = clientID), another called clientFamily (pk = clientFamilyID) and a table that joins those two called client2clientFamily. The latter table only has two rows: clientID and clientFamilyID (no pk).

On the client table, say the client has 3 children and 2 siblings. Those would go on the clientFamily table. The part I don't know how to do is how to assign those 3 kids and 2 sibs to that particular client. I know I do it via the client2clientFamily table but I don't know the query.

I'm also not sure I'm thinking of this right. The fields on clientFamily start off like:

clientFamilyID
spouseFirstName
spouseMiddleName
spouseLastName
spouseOccupation
motherFirstName
motherLastName
motherOccupation
fatherFirstName
fatherLastName
fatherOccupation

I think that part's ok, but if I add:

childFirstName
childLastName
childBirthday
childSex

and

sibFirstName
sibLastName
sibOccupation

how are, for example, a child's birthday and sex are going to be linked to the correct child? And a sib's occupation linked to the correct sib? Now I'm wondering if I need a separate table for clientChildren (and a joining table client2clientChildren) and another one for clientSibs (& client2clientSibs) instead of putting everything in clientFamily. Ugh.

View Replies !   View Related

---

I Want To Display Three Join Tables?
I want to display three joined tables how?

Let say they have connected all with $id as FOREIGN KEY.

Could you show me the right syntax for this to display their contents.
Using SELECT.

View Replies !   View Related

---

Join Two Tables + Where Clause
table Map contains 'ip', 'hash' and table Track contains 'hash' and 'page'. im now trying to select all pages where the ip address is one of eleven possible ones. im using the hash column as the link to tie these two together

but, im not getting the results i was hoping for since it also selects ip addresses that are not in my where clause. what am i doing wrong?

Code:
SELECT map.ip, track.page FROM track join map on map.hash = track.hash
where map.ip = '202.157.181.94'
OR '203.142.12.4'
OR '117.120.9.38'
OR '77.37.2.96'
OR '83.136.67.2'
OR '67.28.127.185'
OR '216.246.50.130'
OR '64.79.136.185'
OR '208.70.76.88'
OR '205.162.234.107'
OR '207.218.123.185'

View Replies !   View Related

---

DISTINCT With Multiple Tables And INNER JOIN ?
My query works OK this way, but I need every "kohde_name" only once
(preferred the earliest "hav_date").

I've tried with "DISTINCT kohde_name" in many ways, but always end up with
syntax error...:(
Is there any solution with DISTINCT, or do I need anotjer solution?

my (simplified) query:
SELECT henk_name,henk_number, kohde_name
,kohde_txt,havainto_id,hav_kohdetxt, hav_date
FROM ((henkilo INNER JOIN havaitsija ON hja_henkilo_id=henkilo_id)
INNER JOIN havainto ON hja_havainto_id=havainto_id)
INNER JOIN kohde ON hav_kohde_id=kohde_id
WHERE ...

View Replies !   View Related

---

Join Optimization & Temporary Tables
--B_3141046513_16095154
Content-type: text/plain; charset="ISO-8859-1"
Content-transfer-encoding: quoted-printable

I=B9m wondering what (in general) causes a join to use temporary tables and
filesorts...
More specifically, what can I tune to avoid it? I can give more info about
a specific situation if anyone=B9s interested.
All the docs say is that you are likely to get a =B3Using temporary=B2 if you
sort on a different column set than the =B3group by=B2, but I=B9m not doing
that...something else must be triggering it.

View Replies !   View Related

---

Join Tables On A Large Database 200 Meg
i am trying to work out the most efficient way to list say multipl=
e categories of entries, the database is quite large about 200 meg.=20

I would like to know if using join tables is more efficient than storing th=
e keys in a varchar field then within the second loop doing a where in (1,2=
,3,4) where the 1,2,3,4 are the stored category keys in the varchar field =
rather than a where in (1), where 1 is the pirmaryID of the entry for insta=
nce ?

View Replies !   View Related

---

Join 3 Tables - Distinct Results
I've got a database that keeps track of sales of widgets. Each company that belongs to my organiztion is to report their widget sales or no sales every month.

There are several different types of widgets. Not all companies sell or report all types of widgets.

We want to report how many companies have reported or not reported their sales (ie. x companies of a possible y companies have reported sales this month - y will always be the same - lets say 5).

Because of the way that sales are input, "big widgets" are reported in 2 different tables called widgets_a and widgets_b. If they don't have any sales to report, they still report and it goes into a table called no_reports. Each table has a couple of common fields - ManufacturerID
and OrderDate.

I can search all of the tables individually to find if a manufacturer has reported -

SELECT DISTINCT ManufacturerID FROM widgets_a WHERE OrderDate >='2003-06-01' AND OrderDate <= '2003-06-30';

but I want to search through the 3 tables and find how many distinct manufacturers have reported in the given month.

View Replies !   View Related

---