Help about MySQL Query

Help about MySQL Query
0

#1

Hi guys

I have two tables;
users:

Vehicle1
Vehicle2
Vehicle3
Vehicle4
Vehicle5

vehicle:
ID

(I’m just give you information which you might need…)

In Vehicle1,Vehicle2… i store ID of vehicles.Now,i want to delete all vehicles which are not on any slot(Vehicle1,Vehice2…) from table users.

So.I need to selet all ID from user table
SELECT Vehicle1,Vehicle2,Vehicle3,Vehicle4,Vehicle5 FROM users

And now delete all ID from vehicle which are not on any slot.I hope you can understand me,sorry for my English.

Thanks


#2

I’m found way to SELECT all Vehicles which are on player slot…But I need to delete vehicles which are not on any slot…

SELECT bu.ID FROM vehicle bu inner join users u where u.Vehicle1 = bu.ID || u.Vehicle2 = bu.ID || u.Vehicle3 = bu.ID || u.Vehicle4 = bu.ID || u.Vehicle5 = bu.ID


#3

So you have users table. Are the Vehicle1, Vehicle2, Vehicle3, Vehicle4, and Vehicle5 field names in the users table? If they are fields names, what kind if values are stored in these fields. If these are values in a field, then what is the field name?

It might be best if you can provide the schema for the database. You do not have to provide any data, but it would help to understand the tables and all the fields and any relationships which may exist between the tables.


#4

Yes,Vehicle1,Vehicle2… are field names in users table.

There I store ID of Vehicles

So I need to delete all Vehicle ID which don’t exist in any field name from users table(Vehicle1,Vehicle2…)

Can you undestand now?


#5

Did you structure the data this way on purpose or is this just data you are having to deal with? I ask, because I do not understand why the same ID of a vehicle would be in more than one of the fields anyway.

Actually, that does not make any sense. If a vehicle does not exist in any of the fields, then there would be nothing to delete from those fields. I assume you mean you want to delete any ID from the vehicle table which does not appear in any of the other 5 tables?


#6

Yes,i want to say it.But my English is bad,sorry.


#7

Try this:

DELETE FROM vehicle where ID NOT IN (
  SELECT DISTINCT vehicle1 FROM users
  UNION SELECT vehicle2 FROM users
  UNION SELECT vehicle3 FROM users
  UNION SELECT vehicle4 FROM users
  UNION SELECT vehicle5 FROM users
);

#8

Something is not good.
I have 8 vehicles after it query(I can’t have 8 if there have just 5 slots…)


#9

I don’t know what you mean by a slot. It would be nice if you could show a sample of data which is in the users table and a sample of data in the vehicle table.


#10

Slot =
Vehicle1
Vehicle2
Vehicle3
Vehicle4
Vehicle5

So there have 5 slots,right?I mean on it…

For example for my user account:
Vehicle1 = 10
Vehicle2 = 33
Vehicle3 = 45
Vehicle4 = 55
Vehicle5 = 66

In Vehicle Table

ID = 10 , Price= 50$
ID = 33 , Price = 85$
ID = 45 , Price 43 $
ID = 55, Price 31 $
ID = 66 , Price 303 $
ID = 109 ,Price 4420 $ etc…

As you can see,all ID(From user table) expect 109 exist.So I want to delete 109 ID from vehicle table…


#11

You originally told me there were 5 fields in the users table with 5 different names (Vehicle1, Vehicle2, Vehicle3, Vehicle4, Vehicle5). Do you mean 5 records in the users table with only a single field named “Slot”. You really have to make sure you understand the correct terminology here or take a screen shot of your table data, so I can see the structure.

I assume your vehicle table looks something like:

vehicle table
ID price
10 50
33 85
45 43
55 31
66 303
109 4420

But I am still confused on what your users table looks like.

I imagine it either looks like:

users table
Slot
10
33
45
55
65

OR

users table
userID Vehicle1 Vehicle2 Vehicle3 Vehicle4 Vehicle5
1 10 33 45 55 65
2 11 36 77 88 100
3 200 230 66 78 90
4 44 71 106 210 330

#12

vehicle table: yes
User table: second :slight_smile:


#13

Your screenshot shows a field named vehiclenumber. Which table is this?


#14

It’s not field.I’m just do this:
SELECT COUNT(ID) as vehiclenumber FROM vehicle WHERE name = 'PaulC'

I’m count because after query for delete i can’t have more than 5 vehicles.


#15

I noticed your count query is for a specific user. You did not say you wanted to delete vehicle IDs for a specify user. The query I gave you would be across all users. Also, I thought the vehicle table only had two fields (ID and price). Now you say there is a 3rd field called “name”? Unless you give me the full schema of your database, I will not be able to assist your any further.


#16

Of course,i know it and I want it :slight_smile:

This Count is from vehicle table :slight_smile:
But It don’t work…

Do you agree with me:
If I have
Vehicle1
Vehicle2
Vehicle3
Vehicle4
Vehicle5

And If I make query good(That delete all ID which are not on any of this slots(vehicle1,vehicle2…) that I can’t have more than 5 vehicles? There is problem,after query;i have 8 vehicles.

After your query:
SELECT ID FROMvehicleWHERE name = 'PaulC'
Result:

ID

  • 15
  • 190
  • 378
  • 21
  • 22
  • 99
  • 110
  • 126

SELECT Vehicel1,Vehicel2,Vehicel3,Vehicel4,Vehicle5 FROM users WHERE name = 'Paul_Castellano'

Result:

  • Vehicle1: 22
  • Vehicle2: 99
  • Vehicle3 : 110
  • Vehicle4: 126
  • Vehicle5: 0(I don’t have Vehicle5)

So…
ID 15,190,378,21 are not on any slot(vehicle1,vehicle2…) and they are not deleted…I don’t know why.

EDIT:

"You did not say you wanted to delete vehicle IDs for a specify user. "

I want to delete for all users.I’m just use my account for examples!


#17

When you ran the SQL code I gave you earlier, did it not show an error? I noticed you had a capital V instead of a lowercase v for the 5 fields in users. I have corrected my original query to be a capital V for each field name, so this should work. I recreated the following tables:

users table
userID Vehicle1 Vehicle2 Vehicle3 Vehicle4 Vehicle5
1 22 99 110 126 0

vehicle table
ID
15
190
378
21
22
99
110
126

After the following SQL exectutes, IDs 15, 190, 378, 21 will be deleted from the vehicle table, leaving only IDs 22, 99, 110, 126

DELETE FROM vehicle where ID NOT IN (
  SELECT DISTINCT Vehicle1 FROM users
  UNION SELECT Vehicle2 FROM users
  UNION SELECT Vehicle3 FROM users
  UNION SELECT Vehicle4 FROM users
  UNION SELECT Vehicle5 FROM users
);

#18

I’m so sorry!
It’s my bad.
Now I’m check and ID: 15,190,378,21 have on another user account(This is my test database so…Some things are mixed…)
Sorry for your time,I’m also lost my day with this…Query is good and it will work on my main database.