Selecting more than one mysql table (join?)

Wimnat

Wimnat

Wimnat

Associate
Joined
19 Jun 2006
Posts
162
Location
Swansea, Wales
I have two mysql tables. Both tables have three fields which have the same names... "version", "type" and "id".

I want to select the version and type columns from both tables if either table has an id = $id.

I used a join statement...

Code: 
SELECT rtcp_sr_packets.uniqueid, rtcp_sr_packets.Version, rtcp_sr_packets.Type, rtcp_sdes_packets.uniqueid, rtcp_sdes_packets.Version, rtcp_sdes_packets.Type FROM `rtcp_sr_packets`, `rtcp_sdes_packets` WHERE rtcp_sr_packets.`Call-ID` = 'Call-ID:f6ea990c-5bfe-db11-892e-0014a411311e@robuntu';

Then i displayed each row of the result in php as normal but instead of showing the four different packets it should have, it just shows two packets and then the same two packets again.

Not sure where I'm going wrong with this one. A little help...! :confused:
 
Is it guaranteed that if there's an entry in table A with id=$id that there's also a row in table B with id also = $id? If so, then why aren't you storing both bits of data in the same table? If not, then what do you want the last two fields to return when it finds a match in table A and not table B?

*edit: You can use an inner join:

Code: 
select a.col1,b.col1 from a inner join b on a.id=b.id where a.id=$id

However, if it doesn't exist in table a, it won't return anything.
 
Last edited:
I can't put the data in the same table because after the first three fields the data is different.

Table 1: id, ver, type, x, y, z
Table 2: id, ver, type, a, b, c

I want to create a list of all the entries in table 1 and 2 where id = $id, displaying the ver and the type.

Then, when I click on one of the items in the list I can query the specific table to get the rest of the fields.
 
Personally, sounds like you want two queries and just keep it simple - when the user selects a particular row, how will you know which table the data came from otherwise?

There's probably a clever way to do this in one query, but I can't think what that is :)
 
Well the type field is unique to the table so i can just look at what type is returned to know which table i need to query further.

Two queries may be the way to go though.
 
Wimnat said:
Well the type field is unique to the table so i can just look at what type is returned to know which table i need to query further.

Two queries may be the way to go though.
Click to expand...

No, im certain you can do it with 1 query. If you could post/email the dump of the database I will have a go at it. Or edit the dump if theres private data on it i'l try get it working for you.

Saw this thread several times today but can't see the problem so gimmi! :)
 
It works fine for me. Maybe tidy up your code abit you will spot the error

Code: 
SELECT `rtcp_sr_packets`.`uniqueid`, `rtcp_sr_packets`.`Version`, `rtcp_sr_packets`.`Type`, `rtcp_sdes_packets`.`uniqueid`, `rtcp_sdes_packets`.`Version`, `rtcp_sdes_packets`.`Type`
FROM `rtcp_sr_packets`, `rtcp_sdes_packets`
WHERE `rtcp_sr_packets`.`Call-ID` = 'Call-ID:f6ea990c-5bfe-db11-892e-0014a411311e@robuntu';

If your getting the same row twice, make sure you aren't getting the same ID `id` <> `id` etc.

I don't fully understand what you are trying to do so I cant offer anything more at this point.
 
Just so everyone knows, the problem was cracked with a good old UNION ALL statement :D

Thanks very much to Jaffa_Cake!
 
You must log in or register to reply here.
Back
Top Bottom