CODEWITHSUNDEEP
mysql
Shift 'n Tab
Shift 'n Tab

Reputation: 9443

How to add multiple rows to multiple references

Assuming i have 2 tables in my database. person and person_purchased_item.

person

id     name       has_purchased
1      Joe        1
2      Mark       0
3      Harry      0
4      Norly      1
5      Freya      0

person_purchased_item

id     person_id     item_code
1      1             1001
2      1             1002
3      1             1003
4      1             1004
5      4             1005

In this case i would like to get the purchased items of a particular person, and also query those who never made any purchase.

case 1

query:

SELECT 
    person.id, person.name, person_purchased_item.item_code 
FROM person INNER JOIN person_purchased_item
    ON person.id = person_purchased_item.person_id
WHERE has_purchased = 1 AND id = 1;

results:

id     name          item_code
1      Joe           1001
1      Joe           1002
1      Joe           1003
1      Joe           1004

case 2

query:

SELECT * FROM person WHERE has_purchased <> 1;

results:

id     name       has_purchased
2      Mark       0
3      Harry      0
5      Freya      0

Now here comes the problem:

How can write a query that insert all Joe's purchased items to every person who never made any purchased? So that each one of them will have Joe's purchased items.

Upvotes: 2

Views: 24

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521209

Insert a cross join of Joe's purchase records with all the people who have not yet made a purchase:

INSERT INTO person_purchased_item (person_id, item_code)
SELECT p1.id, p2.item_code
FROM person p1
CROSS JOIN person_purchased_item p2
WHERE
    p1.has_purchased = 0 AND p2.person_id = 1;

Upvotes: 1

Related Questions