MySql LEFT JOIN - too much data

[ahajdar]

Hello,

I'm trying to get data from 2 tables, however, I just want all rows from table 1 and matching info for those rows in table 2. Table 2 has more entries for some id's in table1 but I don't want all of it, and I try to do comparison based on the date type.

The problem is that I cannot seem to limit my return to only single (distinct) rows from table 1, but LEFT JOIN appends data from table 2 and I get multiple rows for id's from table 1. Here is the SQL line I'm using:

SELECT a.iMediaId, a.vchTitle, b.UserId, b.dtLoanDate FROM t_Media AS a LEFT JOIN t_Loan AS b ON a.iMediaId=b.iMediaId WHERE (a.iMediaId='$id') AND (b.dtReturnDate='0000-00-00 00:00:00');

Perhaps someone has a suggestion to solve this problem.

Thanks!

[Lars_Berg]

select distinct a.iMediaId, a.vchTitle, b.UserId, b.dtLoanDate FROM t_Media AS a LEFT JOIN t_Loan AS b ON a.iMediaId=b.iMediaId WHERE (a.iMediaId='$id') AND (b.dtReturnDate='0000-00-00 00:00:00'); 

will remove all duplicates from the result. If that is not sufficient, post some sample data and describe what result you expect.

[ahajdar]

I tried using DISTINCT but it seems to work only on single tables, so your suggestion doesn't work in my case. Some sample data:

t_Media
"Media Id" "Title"
1 Test
2 Test2
3 Test3

t_Loan
"Loan id" "Media Id" "UserId" "dtReturnDate"
1 1 2 0000-00-00 00:00:00
2 1 3 2000-01-01 00:00:00
3 2 2 0000-00-00 00:00:00

If I do the select stated above (with or without DISTINCT keyword) and look for Media Id=1, I will get 2 rows since Loan Id is different. I'm not even sure why it returns me row with dtReturnDate not equal to 0000-00-00....??? I would still in any case want all data from table 1, just if there is data in table 2 that corresponds, to append to appropriate row in table 1.

Thanks.

[cboston]

I have a similar problem. I have two tables with multiple columns each. the first column in each table contains the same information. I am tring to find information from the first column in each table that meet criteria for a column in the first table and information in another column from the second table.

here are the two tables.

mysql> describe host;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| host | char(15) | YES | | NULL | |
| chassis | char(30) | YES | | NULL | |
| ios | char(30) | YES | | NULL | |
| uptime | char(40) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+

mysql> describe modem;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| host | char(12) | YES | | NULL | |
| autoconfig | char(30) | YES | | NULL | |
| modemcap | char(60) | YES | | NULL | |
| modver | char(20) | YES | | NULL | |
+------------+----------+------+-----+---------+-------+

What I need is the hosts that match criteria for the ios in the host table and modemcap in the modem table. how do I form the query to do this?

Thanks for any help.
Chris Boston

[ahajdar]

cboston,

I just solved my problem (2 different variants) and here are my queries so you may want to try them on your code:

Possible Solution 1: (just use DISTINCT on your id in first table - must of course make WHERE clause!)

SELECT DISTINCT a.iMediaId, a.vchTitle FROM t_Media AS a, t_Loan AS b WHERE ((a.iMediaId=b.iMediaId) AND (b.iUserId='$id'))

Possible Solution 2: (use AND in ON part of the LEFT JOIN)
SELECT a.iMediaId, a.vchTitle, b.dtLoanDate FROM t_Media AS a LEFT JOIN t_Loan AS b ON ((a.iMediaId=b.iMediaId) AND (b.chActive='1')) WHERE (a.iMediaId='$id');

Note: I have "chActive" column which tells me that I want to pull that record. If you put this in WHERE clause, than it will NOT work.

I didn't know that you can put "stuff" in ON part of the JOIN but for now it works, and cross-my-fingers that it will not break some day with another version of MySql.

Good Luck!

[cboston]

Thanks for your help. I got it working and it works great, here is the query that I am using....

select a.host, a.ios, b.modemcap from host as a left join modem as b on a.host=b.host where ios = "stuff" and modemcap = "more_stuff";

🙂

[cboston]

But one thing I dont understand is, what does this portion of the query do for me....

on a.host=b.host

[ahajdar]

cboston,

"ON" part of the JOIN tells MySql what column you're actually making a JOIN on. I hope this helps, although I'm not an expert in this case and usually end up writing a complex query and than test it until I realize that I get the results I wanted.

Good luck with the rest of your project.

[cboston]

Yes, that does make sense. I have just started dealing with queries from multiple tables, so I am just learning this part of SQL

Thanks,
Chris Boston