CODEWITHSUNDEEP
mysqlsql
Andrew
Andrew

Reputation: 4441

MySQL Join and Default

I'm still very new to SQL queries and can't quite figure this one out.

I have two tables, one table I'm running a general SELECT ... WHERE, super easy SQL statement.

Ex:

SELECT * from maindata where somedata4 LIKE "%data4.%"

This gives me back a list of all 6 entries below, however I want an additional column to show me if the current userdata.userId has a matching row and to include the amount column of that. If it doesn't have that row to default to a value of 0.

Table: maindata

id | somedata | somedata2 | somedata3 | somedata4
_________________________________________________
1  | data1.1  | data2.1   | data3.1   | data4.1
2  | data1.2  | data2.2   | data3.2   | data4.2
3  | data1.3  | data2.3   | data3.3   | data4.3
4  | data1.4  | data2.4   | data3.4   | data4.4
5  | data1.5  | data2.5   | data3.5   | data4.5
6  | data1.6  | data2.6   | data3.6   | data4.6

Table: userdata

id | itemId | amount | userId
_____________________________
1  | 6      | 4      | 1
2  | 4      | 4      | 26
3  | 4      | 2      | 1

It should search table maindata for WHERE somedata4 LIKE "%data4.%" and on each of those entries look in table userdata for userdata.amount with maindata.id = userdata.itemId WHERE maindata.userId = 1

Here's what I currently have for SQL

SELECT m.*, IFNULL(u.amount,0)
from maindata m
LEFT OUTER JOIN userdata u ON m.id = u.itemId
WHERE m.somedata4 LIKE "%data4.%"

What I'm missing is the filtering of only amounts from userdata.userId = 1, I want the entire list to show as it is in that query.

Expected Results:

id | somedata | somedata2 | somedata3 | somedata4 | amount
__________________________________________________________
1  | data1.1  | data2.1   | data3.1   | data4.1   | 0
2  | data1.2  | data2.2   | data3.2   | data4.2   | 0
3  | data1.3  | data2.3   | data3.3   | data4.3   | 0
4  | data1.4  | data2.4   | data3.4   | data4.4   | 4
5  | data1.5  | data2.5   | data3.5   | data4.5   | 0
6  | data1.6  | data2.6   | data3.6   | data4.6   | 2

Upvotes: 0

Views: 258

Answers (3)

theLibertine
theLibertine

Reputation: 175

I tried this solution but i am not sure if this is what you are looking for. Let me know.

-------EDIT--------

Now I'm sure about what you are looking for. I had just a couple of minutes so I didn't really optimized the code, but this seems to work except for the order of the fields, that can't be really modified since we're operating in two different sets of results.

By the way, this is the query

SELECT m.*, '0' as amount
from maindata m
left outer JOIN userdata u ON m.id = u.itemId
where (u.userid is null) and m.somedata4 LIKE '%data4.%'

UNION

SELECT m.*, u.amount
from maindata m
inner JOIN userdata u ON m.id = u.itemId
where u.userid = 1 and m.somedata4 LIKE '%data4.%'

and this is the updated fiddle

Hope this helps.

Upvotes: 0

Andrew
Andrew

Reputation: 4441

Here is the SQL Query I was looking for (I think):

SELECT m.*, IFNULL(u.amount,0) AS "Amount"
FROM maindata m
LEFT OUTER JOIN userdata u ON m.id = u.itemId AND userid = 1
WHERE m.somedata4 LIKE "%data4.%"

It's giving me the desired results listed above, I just don't know if it's the most efficient way of handling this request.

SQLFiddle Here

Upvotes: 0

void
void

Reputation: 7890

SELECT m.*, IFNULL(u.amount,0) from maindata m LEFT OUTER JOIN
  userdata u ON m.id = u.itemId WHERE m.userId = 1

Upvotes: 1

Related Questions